package com.ccjq.jq167.com.ccjq.jq167.jquerystudent.dao.impl;

import JqueryStudentUtils.DbConnection;
import com.ccjq.jq167.com.ccjq.jq167.jquerystudent.dao.UserDao;
import com.ccjq.jq167.com.ccjq.jq167.jquerystudent.domain.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    public boolean addAll(User user) {
        //添加信息
        Connection con = null;
        PreparedStatement pre = null;
        boolean b = false;
        try {
            con = DbConnection.getConnection();
            String sql = "insert into jquery_student values(?,?,?,?,?,?)";
            pre = con.prepareStatement(sql);
            pre.setString(1, user.getId());
            pre.setString(2, user.getName());
            pre.setInt(3, user.getAge());
            pre.setString(4, user.getSex());
            pre.setString(5, user.getAddress());
            pre.setString(6, user.getSchool());
            int i = pre.executeUpdate();
            if (i > 0) {
                b = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(null, pre, con);
        }

        return b;
    }

    public boolean deleteId(String id) {
        //通过id删除数据
        Connection con = null;
        PreparedStatement pre = null;
        boolean b = false;
        try {
            con = DbConnection.getConnection();
            String sql = "delete from jquery_student where id=?";
            pre = con.prepareStatement(sql);
            pre.setString(1, id);
            int i = pre.executeUpdate();
            if (i > 1) {
                b = true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return b;
    }

    public boolean updateAll(User user) {
        //更新数据
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        boolean b = false;

        try {
            con = DbConnection.getConnection();
            String sql = "update jquery_student set name =?,age=?,sex=?,address=?,school=?where id=?";
            pre = con.prepareStatement(sql);
            pre.setString(1, user.getName());
            pre.setInt(2, user.getAge());
            pre.setString(3, user.getSex());
            pre.setString(4, user.getAddress());
            pre.setString(5, user.getSchool());
            pre.setString(6, user.getId());
            int i = pre.executeUpdate();
            if (i > 0) {
                b = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }

        return b;
    }

    public List<User> queryAll() {
        //查询全部信息
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        List<User> list = new ArrayList<User>();
        try {
            con = DbConnection.getConnection();
            String sql = "select id,name,age,sex,address,school from jquery_student";
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            while (res.next()) {
                User user = new User();
                user.setId(res.getString(1));
                user.setName(res.getString(2));
                user.setAge(res.getInt(3));
                user.setSex(res.getString(4));
                user.setAddress(res.getString(5));
                user.setSchool(res.getString(6));
                list.add(user);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }

        return list;
    }

    public User queryById(String id) {
        //通过id查询数据
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        User user = null;
        try {
            con = DbConnection.getConnection();
            String sql = "select name,age,sex,address,school from jquery_student where id=?";
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            if (res.next()) {
                user = new User();
                user.setId(res.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }

        return user;
    }
}
